<?php

// retreiving amount of closed srs by products for each group
    $query = 'select tse.team, product.val as product, count(sr) as srs
          from srs, product, tse
          where
            product.id = srs.product and
            tse.owner_id = srs.owner and
            tse.owner_id in (' . $ids . ') and
            srs.closed > ' . $from . ' and
            srs.closed < ' . $till . '
          group by team, product
          order by team, srs desc;';
    @ $result = mysql_query($query);

    while (@ $res = mysql_fetch_assoc($result)) {
        $productforperiod[$res['team']][$res['product']]['closed'] = $res['srs'];
    }
    @ mysql_free_result($result);

// retreiving amount of opened srs by products

    $query = 'select tse.team, product.val as product, count(sr) as srs
          from srs, product, tse
          where
            product.id = srs.product and
            tse.owner_id = srs.owner and
            tse.owner_id in (' . $ids . ') and
            srs.opened > ' . $from . ' and
            srs.opened < ' . $till . '
          group by team, product
          order by team, srs desc;';
    @ $result = mysql_query($query);

    while (@ $res = mysql_fetch_assoc($result)) {
        $productforperiod[$res['team']][$res['product']]['open'] = $res['srs'];
    }
    @ mysql_free_result($result);

// retreiving amount of srs closed with Tier-3 hands off by product for a team


    $query = 'select tse.team, product.val as product, count(sr) as t3srs
          from srs, product, tse
          where
            product.id = srs.product and
            tse.owner_id = srs.owner and
            tse.owner_id in (' . $ids . ') and
            srs.closed > ' . $from . ' and
            srs.closed < ' . $till . ' and
            srs.tier_t_owner is not null
          group by team, product
          order by team, t3srs desc;';
    @ $result = mysql_query($query);

    while (@ $res = mysql_fetch_assoc($result)) {
        $productforperiod[$res['team']][$res['product']]['tier3'] = $res['t3srs'];
        @ $productforperiod[$res['team']][$res['product']]['tier3per'] = sprintf("%.1f", $res['t3srs'] * 100 / $productforperiod[$res['team']][$res['product']]['closed']);
    }
    @ mysql_free_result($result);

//retreiving amount of srs closed with a KCS doc linked by products for a team

    @ $result = mysql_query('
            select tse.team, product.val as product, count(distinct kcs.sr) as linked
            from kcs, tse, srs, product
            where srs.owner = tse.owner_id and
                  product.id = srs.product and
                  kcs.sr = srs.sr and
                  srs.closed >= ' . $from . ' and
                  srs.closed <= ' . $till . ' and
                  srs.owner in (' . $ids . ')
            group by team, product
            order by team, linked desc;');

    while (@ $res = mysql_fetch_assoc($result)) {
        $productforperiod[$res['team']][$res['product']]['kcs'] = $res['linked'];
        @ $productforperiod[$res['team']][$res['product']]['kcsper'] = sprintf("%.1f", $res['linked'] * 100 / $productforperiod[$res['team']][$res['product']]['closed']);
    }
    @ mysql_free_result($result);

// retreiving average customer satisfactopn marks by products for a team
    $query = 'select tse.team, product.val as product, count(custsat.sr) as surveys,
                      cast(avg(skill) as char(4)) as skill,
                      cast(avg(advised) as char(4)) as advised,
                      cast(avg(professional) as char(4)) as prof,
                      cast(avg(time) as char(4)) as time,
                      cast(avg(efect) as char(4)) as efect,
                      cast(avg(overall) as char(4)) as overall
            from custsat, tse, srs, product
            where
                      srs.sr = custsat.sr and
                      srs.product = product.id and
                      tse.owner_id = custsat.sr_owner and
                      custsat.survey_date >= ' . $from . ' and
                      custsat.survey_date <= ' . $till . ' and
                      custsat.sr_owner in (' . $ids . ')
             group by team ,product
             order by team, surveys desc;';
    @ $result = mysql_query($query);

    while (@ $res = mysql_fetch_assoc($result)) {
        $productforperiod[$res['team']][$res['product']]['surveys'] = $res['surveys'];
        $productforperiod[$res['team']][$res['product']]['skill'] = $res['skill'];
        $productforperiod[$res['team']][$res['product']]['advised'] = $res['advised'];
        $productforperiod[$res['team']][$res['product']]['prof'] = $res['prof'];
        $productforperiod[$res['team']][$res['product']]['time'] = $res['time'];
        $productforperiod[$res['team']][$res['product']]['efect'] = $res['efect'];
        $productforperiod[$res['team']][$res['product']]['overall'] = $res['overall'];
    }
    @ mysql_free_result($result);

//Amaount of opened SR's
//select custregion.val as region, 
//       product.val as product, 
//       from_unixtime(srs.opened, '%Y') as opendate,
//       count(sr)
//from srs, product, custregion
//where custregion.id = srs.end_user_support_group
//    AND product.id=srs.product 
//    --AND from_unixtime(srs.opened, '%Y.%m') like '2012.%'
//    AND product.val in ('Call Concentrator','Real-Time Metrics Engine','Genesys Interactive Insights','CC Analyzer/CCPulse+','Genesys Info Mart','Interaction Concentrator','Advisors Platform','Contact Center/Workforce Advisor','Advisors Genesys Adapter','Frontline/Agent Advisor'  )
//    AND custregion.val = 'EMEA'
//group by region, product, opendate;

    
?>
